package com.pk.platform.business.report.service.impl;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.List;
import java.util.Map;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.VerticalAlignment;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import com.pk.platform.business.report.dao.ITuitionReportDao;
import com.pk.platform.business.report.service.ITuitionReportService;
import com.pk.platform.business.report.vo.TuitionReportVO;
import com.pk.platform.util.page.ListPage;
import com.pk.platform.util.page.Pager;

public class TuitionReportServiceImpl implements ITuitionReportService {

	private ITuitionReportDao tuitionReportDao;

	public ListPage getTuitionReportList(TuitionReportVO t, Pager pager,
			int showType) {
		return tuitionReportDao.getTuitionReportList(t, pager, showType);
	}

	public List<Map<String, Object>> getReportTotalList(TuitionReportVO t,int showType){
		return tuitionReportDao.getReportTotalList(t, showType);
	}
	
	public List<Map<String, Object>> getYears(){
		return tuitionReportDao.getYears();
	}
	
	public List<Map<String, Object>> getPeriodAndCharge(String year) {
		return tuitionReportDao.getPeriodAndCharge(year);
	}
	
	public List<Map<String, Object>> getCharge() {
		return tuitionReportDao.getCharge();
	}

	public InputStream getExcelInputStream(TuitionReportVO t,int showType) {
		// 将OutputStream转化为InputStream
		ByteArrayOutputStream out = new ByteArrayOutputStream();
		putDataOnOutputStream(out,t,showType);
		return new ByteArrayInputStream(out.toByteArray());
	}

	private void putDataOnOutputStream(OutputStream os,TuitionReportVO t,int showType) {
		WritableWorkbook workbook = null;
		try {
			workbook = Workbook.createWorkbook(os);
			List<Map<String, Object>> titleList = tuitionReportDao.getCharge();
			List<Map<String, Object>> resultList=tuitionReportDao.getReportList(t, showType);
			WritableFont wf1 = new WritableFont(WritableFont.TIMES, 13,	WritableFont.BOLD);
			WritableCellFormat wcf1 = new WritableCellFormat(wf1);
			//wcf1.setBackground(jxl.format.Colour.LIGHT_GREEN);
			wcf1.setAlignment(Alignment.CENTRE);
			wcf1.setVerticalAlignment(VerticalAlignment.CENTRE);
			wcf1.setWrap(true);
			WritableFont wf2 = new WritableFont(WritableFont.TIMES, 11,	WritableFont.BOLD);
			WritableCellFormat wcf2 = new WritableCellFormat(wf2);
			//wcf2.setBackground(jxl.format.Colour.LIGHT_GREEN);
			wcf2.setAlignment(Alignment.CENTRE);
			wcf2.setVerticalAlignment(VerticalAlignment.CENTRE);
			WritableCellFormat wcf3 = new WritableCellFormat();
			//wcf3.setBackground(jxl.format.Colour.LIGHT_GREEN);
			
			WritableSheet sheet = workbook.createSheet("保教费缴费报表", 0);
			jxl.write.Label lb = null;
			jxl.write.Number nb = null;
			//DecimalFormat df = new DecimalFormat("###,###,###.00");
			Map map,map1;
			int n = 0;
			sheet.mergeCells(0, 0, titleList.size(), 0);
			lb = new jxl.write.Label(0, 0, "东伶珑      保教费明细表",wcf1);
			sheet.addCell(lb);
			lb = new jxl.write.Label(n++, 1, "班次",wcf2);
			sheet.addCell(lb);
			lb = new jxl.write.Label(n++, 1, "学童编号",wcf2);
			sheet.addCell(lb);
			lb = new jxl.write.Label(n++, 1, "学童姓名",wcf2);
			sheet.addCell(lb);
			lb = new jxl.write.Label(n++, 1, "班级名称",wcf2);
			sheet.addCell(lb);
			lb = new jxl.write.Label(n++, 1, "合计",wcf2);
			sheet.addCell(lb);
			lb = new jxl.write.Label(n++, 1, "年度",wcf2);
			sheet.addCell(lb);
			lb = new jxl.write.Label(n++, 1, "一月",wcf2);
			sheet.addCell(lb);
			lb = new jxl.write.Label(n++, 1, "二月",wcf2);
			sheet.addCell(lb);
			lb = new jxl.write.Label(n++, 1, "三月",wcf2);
			sheet.addCell(lb);
			lb = new jxl.write.Label(n++, 1, "四月",wcf2);
			sheet.addCell(lb);
			lb = new jxl.write.Label(n++, 1, "五月",wcf2);
			sheet.addCell(lb);
			lb = new jxl.write.Label(n++, 1, "六月",wcf2);
			sheet.addCell(lb);
			lb = new jxl.write.Label(n++, 1, "七月",wcf2);
			sheet.addCell(lb);
			lb = new jxl.write.Label(n++, 1, "八月",wcf2);
			sheet.addCell(lb);
			lb = new jxl.write.Label(n++, 1, "九月",wcf2);
			sheet.addCell(lb);
			lb = new jxl.write.Label(n++, 1, "十月",wcf2);
			sheet.addCell(lb);
			lb = new jxl.write.Label(n++, 1, "十一月",wcf2);
			sheet.addCell(lb);
			lb = new jxl.write.Label(n++, 1, "十二月",wcf2);
			sheet.addCell(lb);
			for (int i = 0; i < titleList.size(); i++) {
				map = titleList.get(i);
				lb = new jxl.write.Label(i+n, 1, map.get("indexName").toString(),wcf2);
				sheet.addCell(lb);
			}
			
			for (int i = 0; i < resultList.size(); i++) {
				map = resultList.get(i);
				lb = new jxl.write.Label(0, i+2, map.get("classtype")==null?"":map.get("classtype").toString(),wcf3);
				sheet.addCell(lb);
				lb = new jxl.write.Label(1, i+2, map.get("childnum")==null?"":map.get("childnum").toString());
				sheet.addCell(lb);
				lb = new jxl.write.Label(2, i+2, map.get("childname")==null?"":map.get("childname").toString());
				sheet.addCell(lb);
				lb = new jxl.write.Label(3, i+2, map.get("classname")==null?"":map.get("classname").toString());
				sheet.addCell(lb);
				if(map.get("sumAmount")!=null){
					nb = new jxl.write.Number(4, i+2, Double.parseDouble(map.get("sumAmount").toString()));
					sheet.addCell(nb);
				}
				lb = new jxl.write.Label(5, i+2, map.get("year")==null?"":map.get("year").toString());
				sheet.addCell(lb);
				for (int j = 1; j < 13; j++) {
					if(map.get("column"+j)!=null){
						String value = map.get("column"+j).toString();
						if(!"0".equals(value))
						{
							nb = new jxl.write.Number(5+j, i+2, Integer.parseInt(value));
							sheet.addCell(nb);
						}
					}
				}
				
				for (int s = 0; s < titleList.size(); s++) {
					map1 = titleList.get(s);
					if(map.get(map1.get("indexName").toString())!=null){
						String value = map.get(map1.get("indexName").toString()).toString();
						if(!"0".equals(value))
						{
							nb = new jxl.write.Number(18+s, i+2, Integer.parseInt(value));
							sheet.addCell(nb);
						}
					}
				}
				
			}
			workbook.write();
		} catch (Exception e) {
			e.printStackTrace();
		}
		finally
		{
			try
			{
				workbook.close();
			}
			catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	
	public ITuitionReportDao getTuitionReportDao() {
		return tuitionReportDao;
	}

	public void setTuitionReportDao(ITuitionReportDao tuitionReportDao) {
		this.tuitionReportDao = tuitionReportDao;
	}

}
